﻿/*
 * Created by SharpDevelop.
 * User: CeCi
 * Date: 02/04/2014
 * Time: 06:08 p.m.
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.Collections.Generic;
using Gestión_UTN.Entities;

namespace Gestión_UTN.daimpl
{
	/// <summary>
	/// Description of materiaProfesordaimpl.
	/// </summary>
	public class materiaProfesordaimpl:Gestión_UTN.da.materiaProfesorda
	{
		public materiaProfesordaimpl()
		{
		}
		
		public bool SaveMateriaProfesor(int mat_id, int pro_dni, int cur_id)
		{
			bool ingresada = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "INSERT INTO mxp_materiasProfesores(mat_id, pro_dni, cur_id)" +
                    			" VALUES(@mat_id, @pro_dni, @cur_id)";
					
					OleDbCommand insertar = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					
					object[] values = {mat_id, pro_dni, cur_id};
					
					insertar.Parameters.AddRange(values);
					
					insertar.ExecuteNonQuery();
					
					trans.Commit();
					
					ingresada = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				ingresada = false;
				trans.Rollback();
				conex.Close();
			}
			
			return ingresada;
		}
		
		public bool DeleteMateriaProfesor(int mat_id, int cur_id)
		{
			bool eliminada = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "DELETE FROM mxp_materiasProfesores " +
						"WHERE mat_id = @mat_id AND cur_id = @cur_id";
					
					OleDbCommand eliminar = new OleDbCommand(sql,conex);
					
					conex.Open();
					
					trans = conex.BeginTransaction();
					
					eliminar.Parameters.AddWithValue("@mat_id",mat_id);
					eliminar.Parameters.AddWithValue("@cur_id",cur_id);
					
					eliminar.ExecuteNonQuery();
					
					trans.Commit();
					
					eliminada = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				eliminada = false;
				trans.Rollback();
				conex.Close();
			}
			
			return eliminada;
		}
		
		public bool ModifyMateriaProfesor(int mat_id_anterior, int pro_dni_anterior, int cur_id_anterior, int mat_id, int pro_dni, int cur_id)
		{
			bool modificada = false;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "UPDATE mxp_materiasProfesores SET mat_id = @mat_id, pro_dni = @pro_dni, cur_id = @cur_id " +
									"WHERE mat_id = @mat_id_anterior AND pro_dni = @pro_dni_anterior AND cur_id = @cur_id_anterior";
					
					OleDbCommand modificar = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					
					object[] values = {mat_id,pro_dni,cur_id,mat_id_anterior,pro_dni_anterior,cur_id_anterior};
					
					modificar.Parameters.AddRange(values);
					
					modificar.ExecuteNonQuery();
					
					trans.Commit();
					
					modificada = true;
					
					conex.Close();
				}
				
			} catch (Exception) {
				modificada = false;
				trans.Rollback();
				conex.Close();
			}
			
			return modificada;
		}
		
		public System.Data.DataTable FindAllMateriaProfesor()
		{
			DataTable tablaMatPro = null;
			
			OleDbConnection conex = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				tablaMatPro = new DataTable();
				
				if (null != conex) {
					
					string sql = "SELECT mat_nombre, pro_dni, pro_nombre, pro_apellido, cur_anio, cur_cuatrimestre" +
									" FROM mxp_materiasProfesores mxp, mat_materias mat, pro_profesores pro, cur_cursadas cur" +
									" WHERE mxp.mat_id = mat.mat_id AND mxp.pro_dni = pro.pro_dni AND mxp.cur_id = cur.cur_id";
					
					 OleDbDataAdapter adaptador = new OleDbDataAdapter(sql, conex);
					 
					 conex.Open();
					 
					 adaptador.Fill(tablaMatPro,"materiasProfesores");
					
					 conex.Close();
				}
				
			} catch (Exception) {
				
				tablaMatPro = null;
				conex.Close();
			}
			
			return tablaMatPro;
		}
		
		public System.Collections.Generic.List<Gestión_UTN.Entities.MateriaProfesor> FindMateriaProfesorByDni(int pro_dni)
		{
			List<MateriaProfesor> matPro = null;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "SELECT mat_id, pro_dni, cur_id" +
									" FROM mxp_materiasProfesores mxp WHERE pro_dni = @pro_dni";
					
					OleDbCommand consulta = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					 
					consulta.Parameters.AddWithValue("@pro_dni",pro_dni);
					
					OleDbDataReader lector = consulta.ExecuteReader();
					
					while (lector.Read()) 
					{
						MateriaProfesor mp = new MateriaProfesor(Convert.ToInt16(lector[0].ToString()),
						                                         Convert.ToInt16(lector[1].ToString()),
						                                         Convert.ToInt16(lector[2].ToString()));
						
						matPro.Add(mp);
						
						mp = null;
					}
					
					 conex.Close();
				}
				
			} catch (Exception) {
				
				trans.Rollback();
				matPro = null;
				conex.Close();
			}
			
			return matPro;
		}
		
		public System.Collections.Generic.List<Gestión_UTN.Entities.MateriaProfesor> FindMateriaProfesorByMateria(int mat_id)
		{
			List<MateriaProfesor> matPro = null;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "SELECT mat_id, pro_dni, cur_id" +
									" FROM mxp_materiasProfesores mxp WHERE mat_id = @mat_id";
					
					OleDbCommand consulta = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					 
					consulta.Parameters.AddWithValue("@mat_id",mat_id);
					
					OleDbDataReader lector = consulta.ExecuteReader();
					
					while (lector.Read()) 
					{
						MateriaProfesor mp = new MateriaProfesor(Convert.ToInt16(lector[0].ToString()),
						                                         Convert.ToInt16(lector[1].ToString()),
						                                         Convert.ToInt16(lector[2].ToString()));
						
						matPro.Add(mp);
						
						mp = null;
					}
					
					 conex.Close();
				}
				
			} catch (Exception) {
				
				trans.Rollback();
				matPro = null;
				conex.Close();
			}
			
			return matPro;
		}
		
		public System.Collections.Generic.List<Gestión_UTN.Entities.MateriaProfesor> FindMateriaProfesorByCursada(int cur_id)
		{
			List<MateriaProfesor> matPro = null;
			
			OleDbConnection conex = null;
			OleDbTransaction trans = null;
			
			try
			{
				conex = util.Conexion.conectarAccess();
				trans = null;
				
				if (null != conex) {
					
					string sql = "SELECT mat_id, pro_dni, cur_id" +
									" FROM mxp_materiasProfesores mxp WHERE cur_id = @cur_id";
					
					OleDbCommand consulta = new OleDbCommand(sql,conex);
					
					conex.Open();
					trans = conex.BeginTransaction();
					 
					consulta.Parameters.AddWithValue("@cur_id",cur_id);
					
					OleDbDataReader lector = consulta.ExecuteReader();
					
					while (lector.Read()) 
					{
						MateriaProfesor mp = new MateriaProfesor(Convert.ToInt16(lector[0].ToString()),
						                                         Convert.ToInt16(lector[1].ToString()),
						                                         Convert.ToInt16(lector[2].ToString()));
						
						matPro.Add(mp);
						
						mp = null;
					}
					
					 conex.Close();
				}
				
			} catch (Exception) {
				
				trans.Rollback();
				matPro = null;
				conex.Close();
			}
			
			return matPro;
		}
	}
}
